﻿using System;
using Dapper;
using ML.Entity;
using ML.IRepository;
using System.Linq;
using System.Collections.Generic;
using MySql.Data.MySqlClient;

namespace ML.Repository
{
    public class OrderRepository : DBFactory, IOrderRepository
    {
        public Pager<OrderResponse> List(OrderSearch orderSearch)
        {
            //组装调用存储过程参数
            var criteria = new PageCriteria() { TableName = "ml_order o INNER JOIN ml_user u on o.UserId = u.Id INNER JOIN ml_merchant m on o.MerchantId = m.Id" };
            if (!string.IsNullOrEmpty(orderSearch.merchantid_search))
            {
                criteria.Conditions += string.Format(" AND m.Id='{0}'", orderSearch.merchantid_search);
            }
            if (!string.IsNullOrEmpty(orderSearch.merchantname_search))
            {
                criteria.Conditions += string.Format(" AND m.Name LIKE '%{0}%'", orderSearch.merchantname_search);
            }
            if (!string.IsNullOrEmpty(orderSearch.ordernumber_search))
            {
                criteria.Conditions += string.Format(" AND o.OrderNumber LIKE '%{0}%'", orderSearch.ordernumber_search);
            }
            if (orderSearch.state_search != -1)
            {
                criteria.Conditions += string.Format(" AND o.State={0}", orderSearch.state_search);
            }
            if (orderSearch.paystate_search != -1)
            {
                criteria.Conditions += string.Format(" AND o.PayState={0}", orderSearch.paystate_search);
            }
            criteria.Conditions += string.Format(" AND o.DelMark=0");

            criteria.PageIndex = orderSearch.pageindex;
            criteria.Field = @"u.NickName,m.Name,o.*";
            criteria.PageSize = orderSearch.pagesize;
            criteria.SortName = orderSearch.ordername;
            if (orderSearch.orderby)
            {
                criteria.Sort = "DESC";
            }
            else
            {
                criteria.Sort = "ASC";
            }

            //调用存储过程进行查询并且返回
            var list = GetPageData<OrderResponse>(criteria);
            return list;
        }

        public OrderResponse GetDetailById(string id)
        {
            const string sql = @"SELECT u.NickName,m.Name,m.Logo,m.DistributionFee,ra.Id AS AddressId,ra.ContactName,ra.Contact,ra.Address,o.*  FROM ml_order o
	                INNER JOIN ml_user u on o.UserId=u.Id
	                INNER JOIN ml_merchant m on o.MerchantId=m.Id
	                LEFT JOIN ml_receivingaddress ra on o.ReceivingAddressId=ra.Id
                WHERE o.Id=@Id";
            using (_db)
            {
                return _db.Query<OrderResponse>(sql, new { @Id = id }).FirstOrDefault();
            }
        }

        public OrderResponse GetInfoById(string id)
        {
            const string sql = @"SELECT m.Name,o.*  FROM ml_order o INNER JOIN ml_merchant m on o.MerchantId=m.Id WHERE o.Id=@Id";
            using (_db)
            {
                return _db.Query<OrderResponse>(sql, new { @Id = id }).FirstOrDefault();
            }
        }

        public OrderEntity GetById(string id)
        {
            const string sql = @"SELECT  * FROM ml_order WHERE Id=@Id";
            using (_db)
            {
                return _db.Query<OrderResponse>(sql, new { @Id = id }).FirstOrDefault();
            }
        }

        public IEnumerable<OrderResponse> GetListByUserId(string id)
        {
            const string sql = @"SELECT m.Name,m.Logo,o.* FROM ml_order o
                INNER JOIN ml_merchant m on o.MerchantId=m.Id AND o.DelMark=0 AND o.UserId=@UserId ORDER BY o.CreateTime DESC";
            using (_db)
            {
                return _db.Query<OrderResponse>(sql, new { UserId = id });
            }
        }

        public IEnumerable<OrderResponse> GetListByState(string state)
        {
            const string sql = @"SELECT u.NickName,m.Name,m.Logo,ra.ContactName,ra.Contact,ra.Address,o.*  FROM ml_order o
	                INNER JOIN ml_user u on o.UserId=u.Id
	                INNER JOIN ml_merchant m on o.MerchantId=m.Id
	                LEFT JOIN ml_receivingaddress ra on o.ReceivingAddressId=ra.Id
                WHERE o.State=@State";
            using (_db)
            {
                return _db.Query<OrderResponse>(sql, new { @State = state });
            }
        }

        public IEnumerable<OrderEntity> GetOrderInfoByMerchantId(string id)
        {
            const string sql = @"SELECT  * FROM ml_order WHERE MerchantId=@MerchantId";
            using (_db)
            {
                return _db.Query<OrderEntity>(sql, new { @MerchantId = id });
            }
        }

        public IEnumerable<OrderEntity> GetTrueOrderInfoByMerchantId(string merchantId)
        {
            string sql = @"SELECT  * FROM ml_order WHERE State=3 AND PayState=1 AND DelMark=0";
            if (merchantId != "-1" && !string.IsNullOrEmpty(merchantId))
            {
                sql += " AND MerchantId='" + merchantId + "'";
            }
            using (_db)
            {
                return _db.Query<OrderEntity>(sql);
            }
        }

        public IEnumerable<OrderEntity> GetOrderInfoListDays()
        {
            const string sql = @"SELECT * FROM ml_order WHERE State=@State AND PayState=@PayState AND TO_DAYS(NOW()) -TO_DAYS(CreateTime) = 1";
            using (_db)
            {
                return _db.Query<OrderEntity>(sql, new { @State = (int)OrderStatus.processed, @PayState = (int)PayStateStatus.havepaid });
            }
        }

        public int GetPaidOrderByTime(string id, DateTime beginTime, DateTime endTime)
        {
            const string sql = @"SELECT count(*) FROM ml_order WHERE PayState=1 AND MerchantId=@MerchantId AND CreateTime>=@BeginTime AND CreateTime<@EndTime";
            using (_db)
            {
                return _db.QueryFirst<int>(sql, new { @MerchantId = id, @BeginTime = beginTime, @EndTime = endTime });
            }
        }

        public IEnumerable<OrderEntity> GetOrderUnPay(DateTime beginTime, DateTime endTime)
        {
            const string sql = @"SELECT * FROM ml_order WHERE PayState=@PayState AND CreateTime>=@BeginTime AND CreateTime<=@EndTime";
            using (_db)
            {
                return _db.Query<OrderEntity>(sql, new { @PayState = (int)PayStateStatus.unpaid, @BeginTime = beginTime, @EndTime = endTime });
            }
        }

        public IEnumerable<OrderEntity> GetOrderStatistics(string id, DateTime beginTime, DateTime endTime)
        {
            const string sql = @"SELECT * FROM ml_order WHERE PayState=1 AND MerchantId=@MerchantId AND CreateTime>=@BeginTime AND CreateTime<@EndTime";
            using (_db)
            {
                return _db.Query<OrderResponse>(sql, new { @MerchantId = id, @BeginTime = beginTime, @EndTime = endTime });
            }
        }

        public bool Add(OrderEntity entity)
        {
            throw new System.NotImplementedException();
        }

        public bool Add(OrderEntity entity, List<OrderDetailsEntity> orderDetails)
        {
            //两个数据库添加的Sql语句
            const string add_order_sql = @"INSERT INTO ml_order(Id,UserId,MerchantId,Price,DiscountPrice,ActPayPrice,ActPayRMB,ServiceTime,ReceivingAddressId,
                PayMethod,NumberOfMeals,RedPackageType,Description,InvoiceInfo,State,PayState,OrderNumber,DelMark,CreateTime)
                VALUES(@Id,@UserId,@MerchantId,@Price,@DiscountPrice,@ActPayPrice,@ActPayRMB,@ServiceTime,@ReceivingAddressId,@PayMethod,@NumberOfMeals,@RedPackageType,
                @Description,@InvoiceInfo,@State,@PayState,@OrderNumber,@DelMark,@CreateTime)";

            const string add_orderdetail_sql = @"INSERT INTO ml_orderdetails(Id,OrderId,ProductId,ProductPrice,ProductNumber) 
                VALUES(@Id,@OrderId,@ProductId,@ProductPrice,@ProductNumber)";

            //构造事务、要么执行成功要么执行失败
            using (var conn = OpenConnection())
            {
                MySqlTransaction trans = conn.BeginTransaction();
                var isUserTrue = conn.Execute(add_order_sql, entity, trans);
                if (isUserTrue > 0)
                {
                    var isuserInvitationTrue = conn.Execute(add_orderdetail_sql, orderDetails, trans);
                    if (isuserInvitationTrue < 0)
                    {
                        trans.Rollback();
                        return false;
                    }
                    trans.Commit();
                }
                else
                {
                    trans.Rollback();
                    return false;
                }
                return true;
            }
        }

        public bool Update(OrderEntity entity)
        {
            const string sql = @"UPDATE ML_Order SET ReceivingAddressId=@ReceivingAddressId,NumberOfMeals=@NumberOfMeals,Description=@Description WHERE Id=@Id";
            using (_db = OpenConnection())
            {
                if (_db.Execute(sql, entity) > 0)
                {
                    return true;
                }
                return false;
            }
        }

        public bool UpdateState(string id, int state)
        {
            const string sql = @"UPDATE ML_Order SET State=@State WHERE Id=@Id";
            using (_db)
            {
                if (_db.Execute(sql, new { @State = state, @Id = id }) > 0)
                {
                    return true;
                }
                return false;
            }
        }

        public bool UpdatePayState(string id, int payState)
        {
            const string sql = @"UPDATE ML_Order SET PayState=@PayState WHERE Id=@Id";
            using (_db)
            {
                if (_db.Execute(sql, new { @PayState = payState, @Id = id }) > 0)
                {
                    return true;
                }
                return false;
            }
        }

        public bool Delete(OrderEntity entity)
        {
            throw new System.NotImplementedException();
        }

        public bool Delete(string id)
        {
            throw new System.NotImplementedException();
        }
    }
}